package com.uspacex.calculate.service;

import com.alibaba.fastjson.JSON;
import com.uspacex.calculate.entity.requstClass;
import com.uspacex.calculate.entity.sourceItem;

import java.sql.*;
import java.util.*;
import java.util.Date;


public class createSubjectTable {
    private static Connection conn = null;

    private static String uuid = null;

    private static String dbName = null;

    private static String tableName = null;

    private static List totalNum = null;

    private static int limites = 0;

    private static int rowTotal = 0;

    private static int rowProcess = 0;

    private static int pointCount = 0;

    public createSubjectTable() {
        super();
    }

    public Connection getConn() {
        return conn;
    }

    private requstClass requstclass;

    /**
     * 连接指定参数的数据库
     */
    public String connect(String ip, String port, String username, String password, String dbName) throws Exception, IllegalAccessException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String url = "jdbc:mysql://" + ip + ":" + port + "/" + dbName + "?serverTimezone=Hongkong&useUnicode=true&characterEncoding=UTF-8";
        System.out.println(url);
        try {
            conn = DriverManager.getConnection(url, username, password);
            return "succeeded";
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }

    }

    /**
     * 断开连接
     */
    public static void disconnect() throws SQLException {
        if (conn != null) {
            conn.close();
        }
    }

    public ArrayList<String> showTables() throws Exception {
        ArrayList<String> tableName = new ArrayList<String>();
        if (conn != null && !conn.isClosed()) {
//			Statement stmt = conn.createStatement();
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet rs = meta.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                String name = rs.getString(3);
                tableName.add(name);
            }
            rs.close();
            return tableName;
        } else {
            return null;
        }

    }

    /**
     * 跨库获取表内数据并写入主题库中，为主题表生成做准备
     */
    public ArrayList<Map<String, String>> sqlSelect(String sql, int maxRows, String tableName, String targetSchema, int num) throws Exception {
        ArrayList<Map<String, String>> rows = new ArrayList<Map<String, String>>();
        if (conn != null && !conn.isClosed()) {
            try {
                PreparedStatement ps = conn.prepareStatement(sql);
                if (maxRows != 0) {
                    ps.setMaxRows(maxRows);
                }
                ResultSet rs = ps.executeQuery();
                ResultSetMetaData meta = rs.getMetaData();
                int columnCount = meta.getColumnCount();
                rs.last();
                int rowCount = rs.getRow();
                rs.first();
                int count = 1;
                for (int i = 0; i < rowCount; i++) {
                    Map<String, String> columns = new LinkedHashMap<String, String>();
                    String sqlString = "INSERT INTO " + targetSchema + "." + tableName + " (subjectId,";
                    for (int j = 1; j < columnCount + 1; j++) {
                        if (j != columnCount) {
                            sqlString = sqlString + tableName + meta.getColumnName(j) + ",";
                        } else {
                            sqlString = sqlString + tableName + meta.getColumnName(j) + ") VALUES (";
                        }
                        columns.put(meta.getColumnName(j), rs.getString(j));
                    }
                    sqlString = sqlString + count + ",";
                    count++;
                    for (int j = 1; j < columnCount + 1; j++) {
                        if (j != columnCount) {
                            sqlString = sqlString + "'" + rs.getString(j) + "'" + ",";
                        } else {
                            sqlString = sqlString + "'" + rs.getString(j) + "'" + ")";
                        }
                    }
                    rowProcess = i + pointCount;
                    System.out.println("计数" + rowProcess);
                    if (rowProcess >= rowTotal - 1) {
                        rowProcess = 99999;
                    }
                    System.out.println(sqlString);
                    PreparedStatement ps1 = conn.prepareStatement(sqlString);
                    ps1.executeUpdate();
                    ps1.closeOnCompletion();
                    rows.add(columns);
                    rs.next();
                }
                pointCount = pointCount + rowCount;
                //PreparedStatement ps1 = conn.prepareStatement(sqlString);
                //ps1.executeUpdate();
                rs.close();
                ps.closeOnCompletion();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            return rows;
        } else {
            return null;
        }
    }

    /**
     * 获取需要插入主题库的数据行数，为进度监测做准备
     */
    public void check(String sourceTable) throws SQLException {
        if (conn != null && !conn.isClosed()) {
            String sql = "SELECT * FROM " + sourceTable + ";";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            rs.last();
            int count = rs.getRow();
            if (count > limites) {
                count = limites;
            }
            rowTotal += count;
            System.out.println("原行数:" + rs.getRow());
            System.out.println("行数:" + count);
            System.out.println("总数:" + rowTotal);
            int i = 0;
        }
    }

    /**
     * 获得某表中所有字段的注释
     *
     * @param tableName
     * @return
     * @throws Exception
     */
    public Map<String, String> getColumnCommentByTableName(String tableName, List<String> selectitem) throws Exception {
        Map<String, String> map = new LinkedHashMap<>();
        if (conn != null && !conn.isClosed()) {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                if (selectitem.contains(rs.getString("Field"))) {
                    map.put(rs.getString("Field"), rs.getString("Comment"));
                }
            }
            rs.close();
            stmt.close();
            return map;
        } else {
            return null;
        }
    }

    /**
     * 返回注释信息
     *
     * @param all
     * @return
     */
    public static String parse(String all) {
        String comment = null;
        int index = all.indexOf("COMMENT='");
        if (index < 0) {
            return "erro";
        }
        comment = all.substring(index + 9);
        comment = comment.substring(0, comment.length() - 1);
        return comment;
    }

    /**
     * 2019/4/18 20:00
     * 获取表中的字段对应的类型
     *
     * @param tableName
     * @return
     * @throws Exception
     */
    public Map<String, String> getTypeByTableName(String tableName, List<String> selectitem) throws Exception {
        Map<String, String> map = new LinkedHashMap<>();
        if (conn != null && !conn.isClosed()) {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("show COLUMNS from " + tableName);
            while (rs.next()) {
                if (selectitem.contains(rs.getString("Field"))) {
                    map.put(rs.getString("Field"), rs.getString("Type"));
                }
            }
            rs.close();
            stmt.close();
            return map;
        } else {
            return null;
        }
    }

    /**
     * 2019/4/18 20:30
     * 创建主题表所在的数据库
     *
     * @param dbName
     * @return
     * @throws Exception
     */
    public String createDB(String dbName) throws Exception {
        if (conn != null && !conn.isClosed()) {
            Statement stmt = conn.createStatement();
            stmt.execute("CREATE DATABASE " + dbName + " DEFAULT CHARSET utf8 COLLATE utf8_general_ci");
            String flag = "success";
            stmt.close();
            return flag;
        } else {
            return null;
        }
    }

    /**
     * 2019/4/18 20:30
     * 创建主题表
     *
     * @param tableName,Item
     * @return
     * @throws Exception
     */
    public String createTable(String tableName, Map<String, String> Item, Map<String, String> comment) throws Exception {
        if (conn != null && !conn.isClosed()) {
            String sql = "create table " + tableName + " (subjectId int(11) not null primary key,";
            for (String key : Item.keySet()) {
                String value = Item.get(key);
                String commentString = comment.get(key);
                System.out.println(tableName + "," + key + "  " + value + "  " + commentString);
                sql = sql + tableName + key + " " + value + " COMMENT " + "'" + commentString + "',";
            }
            sql = sql.substring(0, sql.length() - 1);
            sql = sql + ");";
            System.out.println(sql);
            Statement stmt = conn.createStatement();
            stmt.execute(sql);
            String flag = "success";
            stmt.close();
            return flag;
        } else {
            return null;
        }
    }

    /**
     * 生成主题表
     */
    public void createSubjectTable1(String targetTableName, List<String> fieldsName, List<String> keysName, List<String> sourceTableName, String targetSchema) throws Exception {
        String b1 = connect("127.0.0.1", "3306", "root", "root", targetSchema);
        if (conn != null && !conn.isClosed()) {
            Statement stmt = conn.createStatement();
            String sqlString = "create table " + targetTableName + " select ";
            sqlString = sqlString + keysName.get(0) + ",";
            for (int i = 0; i < fieldsName.size(); i++) {
                if (i == fieldsName.size() - 1) {
                    sqlString = sqlString + fieldsName.get(i);
                } else {
                    sqlString = sqlString + fieldsName.get(i) + ",";
                }
            }
            sqlString = sqlString + " from " + sourceTableName.get(0) + " ";
            for (int i = 1; i < keysName.size(); i++) {
                sqlString = sqlString + " inner join " + sourceTableName.get(i) + " on " + keysName.get(0) + "=" + keysName.get(i);
            }
            sqlString = sqlString + ";";
            int flag = 1;
            System.out.println(sqlString);
            try {
                stmt.execute(sqlString);
                stmt.close();
            } catch (SQLException e) {
                flag = 0;
                e.printStackTrace();
            }
            if (flag == 1) {
                Statement stmt1 = conn.createStatement();
                String sqlKey = "ALTER TABLE " + targetTableName + " ADD COLUMN subjectId int primary key COMMENT '主键' auto_increment;";
                System.out.println(sqlKey);
                stmt1.execute(sqlKey);
                stmt1.close();
                Timestamp timeStamp = new Timestamp(System.currentTimeMillis());
                updataFinshDate(timeStamp);
            }

        }
    }

    /**
     * 返回当前已插入主题库的行数
     */
    public int checkProcess() {
        return rowProcess;
    }

    /**
     * 获取插入主题库的总行数
     */
    public int totalNum() {
        System.out.println("总数接口" + rowTotal);
        return rowTotal;
    }

    /**
     * 主题表生成完成后更新完成时间以及flag
     */
    public void updataFinshDate(Timestamp date) throws Exception {
        String b = connect("127.0.0.1", "3306", "root", "root", "Dataset");
        String sql = "UPDATE datatargetconf SET timeFinshed=" + date + " WHERE taskId=" + uuid;
        System.out.println(sql);
        PreparedStatement ps = null;
        ps = conn.prepareStatement("UPDATE datatargetconf SET timeFinshed=? ,datatargetProcess=? WHERE taskId=?");
        ps.setTimestamp(1, date);
        ps.setDouble(2, 1.00);
        ps.setString(3, uuid);
        ps.executeUpdate();
        ps.closeOnCompletion();
        disconnect();

    }

    /**
     * 获取数据源的数据库相关配置信息（查datasourceconf表）
     */
    public Map<String, String> sourceConfig(String sql) throws Exception {
        if (conn != null && !conn.isClosed()) {
            try {
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet rs = ps.executeQuery();
                ResultSetMetaData meta = rs.getMetaData();
                Map<String, String> columns = new LinkedHashMap<String, String>();
                int columnCount = meta.getColumnCount();
                rs.first();
                for (int j = 1; j < columnCount + 1; j++) {
                    columns.put(meta.getColumnName(j), rs.getString(j));
                }
                System.out.println(columns);
                return columns;
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
        } else {
            return null;
        }
    }

    /**
     * 2019/4/18 20:30
     * 创建主题表
     *
     * @param tableName,Item
     * @return
     * @throws Exception
     */
    public void alterTable(String tableName, String comment, String targetSchema) throws Exception {
        String b1 = connect("127.0.0.1", "3306", "root", "root", targetSchema);
        if (conn != null && !conn.isClosed()) {
            int count = 0;
            String sql = "alter table " + tableName + " COMMENT = '" + comment + "';";
            System.out.println(sql);
            Statement stmt = conn.createStatement();
            stmt.execute(sql);
            stmt.close();
            disconnect();
        } else {
            System.out.println("erro");
        }
    }

    public void alterKey(String tableName, String targetSchema) throws Exception {
        String b1 = connect("127.0.0.1", "3306", "root", "root", targetSchema);
        if (conn != null && !conn.isClosed()) {
            int count = 0;
            String sql = "ALTER TABLE "+tableName+
                    " ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT,\n" +
                    "ADD PRIMARY KEY (`id`);";
            System.out.println(sql);
            Statement stmt = conn.createStatement();
            stmt.execute(sql);
            stmt.close();
            disconnect();
        } else {
            System.out.println("erro");
        }
    }
    /**
     * 获取数据源的数据表描述，是前端下拉选择数据源的依据
     */
    public List<String> getSourceDescription() throws Exception {
        String sqlString = "SELECT dataSourceDescription FROM datasourceconf";
        List<String> sourceConfig = new ArrayList<>();
        String a = connect("127.0.0.1", "3306", "root", "root", "Dataset");
        if (conn != null && !conn.isClosed()) {
            try {
                PreparedStatement ps = conn.prepareStatement(sqlString);
                ResultSet rs = ps.executeQuery();
                ResultSetMetaData meta = rs.getMetaData();
                int columnCount = meta.getColumnCount();
                while (rs.next()) {
                    String name = rs.getString(1);
                    sourceConfig.add(name);
                }
                System.out.println(sourceConfig);
                return sourceConfig;
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
        } else {
            return null;
        }
    }

    /**
     * 获取主题库的主题表描述，是计算界面的下拉选择数据表的依据
     */
    public List<String> getTargetDescription() throws Exception {
        String sqlString = "SELECT dataTargetDescription FROM datatargetconf";
        List<String> sourceConfig = new ArrayList<>();
        String a = connect("127.0.0.1", "3306", "root", "root", "Dataset");
        return getStrings(sqlString, sourceConfig);
    }

    /**
     * 获取主题数据库的描述，是计算页面下拉选择数据库的依据
     */
    public List<String> getTargetDBName() throws Exception {
        String sqlString = "SELECT dataTargetDBname FROM datatargetconf";
        List<String> sourceConfig = new ArrayList<>();
        String a = connect("127.0.0.1", "3306", "root", "root", "Dataset");
        return getStrings(sqlString, sourceConfig);
    }

    /**
     * 上述两个公用的函数。
     */
    private List<String> getStrings(String sqlString, List<String> sourceConfig) throws SQLException {
        if (conn != null && !conn.isClosed()) {
            try {
                PreparedStatement ps = conn.prepareStatement(sqlString);
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    String name = rs.getString(1);
                    sourceConfig.add(name);
                }
                System.out.println(sourceConfig);
                return sourceConfig;
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
        } else {
            return null;
        }
    }

    /**
     * 生成主题库前的预处理，主要获得插入总数。
     */
    public void preCalculate(Map<String, Object> requestParams, String json, Map<String, String> maps) throws Exception {
        System.out.println(requestParams);
        if (requestParams == null) {
            System.out.println("null");
        } else {
            List<sourceItem> data = JSON.parseArray(JSON.parseObject(json).getString("data"), sourceItem.class);
            int max = data.size();
            limites = Integer.parseInt(maps.get("limites"));
            //limites=limites*max-2;
            for (int i = 0; i < data.size(); i++) {
                String sourceId = data.get(i).getSourceId();
                getTotalNum(sourceId, data.get(i));
            }
        }
    }

    /**
     * 分别获得源数据表的总条目数
     */
    public void getTotalNum(String sourceId, sourceItem data) throws Exception {
        String sqlString = "SELECT * FROM datasourceconf WHERE id=" + sourceId;
        Map<String, String> sourceConfig = new HashMap<String, String>();
        String a = connect("127.0.0.1", "3306", "root", "root", "Dataset");
        sourceConfig = sourceConfig(sqlString);
        List<String> totalFields = new ArrayList<>();
        List<String> keys = data.getKeys();
        List<String> selectItem = data.getField();
        Map<String, String> Item = new LinkedHashMap<>();
        Map<String, String> Comment = new LinkedHashMap<>();
        String currcentTable = data.getSourceTable();
        String currcentUrl = sourceConfig.get("dataSourceUrl");
        String currcentPort = sourceConfig.get("datasourcePort");
        String currcentUserName = sourceConfig.get("dataSourceUserName");
        String currcentPassWord = sourceConfig.get("dataSourceUserPassword");
        String currcentSchema = sourceConfig.get("datasourceDB");
        disconnect();
        String a3 = connect(currcentUrl, currcentPort, currcentUserName, currcentPassWord, currcentSchema);
        check(currcentTable);
        disconnect();
    }

    /**
     * 获取源数据表的配置信息，用来夸库取数据
     */
    public void getSourceConfig(String sourceId, sourceItem data, String limites, String targetSchema, String targetTable, int i) throws Exception {
        String sqlString = "SELECT * FROM datasourceconf WHERE id=" + sourceId;
        Map<String, String> sourceConfig = new HashMap<String, String>();
        String a = connect("127.0.0.1", "3306", "root", "root", "Dataset");
        sourceConfig = sourceConfig(sqlString);
        List<String> totalFields = new ArrayList<>();
        List<String> keys = data.getKeys();
        List<String> selectItem = data.getField();
        Map<String, String> Item = new LinkedHashMap<>();
        Map<String, String> Comment = new LinkedHashMap<>();
        String currcentTable = data.getSourceTable();
        String currcentUrl = sourceConfig.get("dataSourceUrl");
        String currcentPort = sourceConfig.get("datasourcePort");
        String currcentUserName = sourceConfig.get("dataSourceUserName");
        String currcentPassWord = sourceConfig.get("dataSourceUserPassword");
        String currcentSchema = sourceConfig.get("datasourceDB");
        disconnect();
        totalFields.addAll(keys);
        totalFields.addAll(selectItem);
        String a3 = connect(currcentUrl, currcentPort, currcentUserName, currcentPassWord, currcentSchema);
        Item = getTypeByTableName(currcentTable, totalFields);
        Comment = getColumnCommentByTableName(currcentTable, totalFields);
        disconnect();
        String b1 = connect("127.0.0.1", "3306", "root", "root", targetSchema);
        String resultTable = createTable(currcentTable, Item, Comment);
        disconnect();
        String a1 = connect(currcentUrl, currcentPort, currcentUserName, currcentPassWord, currcentSchema);
        String sql = "SELECT ";
        int count = 0;
        for (String K : keys) {
            sql = sql + K + ",";
        }
        for (String S : selectItem) {
            count++;
            if (count != selectItem.size()) {
                sql = sql + S + ",";
            } else {
                sql = sql + S;
            }
        }
        sql = sql + " FROM " + currcentTable + ";";
        System.out.println(sql);
        ArrayList<Map<String, String>> rows = new ArrayList<Map<String, String>>();
        int max = Integer.parseInt(limites);
        ;
        rows = sqlSelect(sql, max, currcentTable, targetSchema, i);
        disconnect();
    }

    /**
     * 创建主题数据库
     */
    public void createTargetDB(String targetDB) throws Exception {
        String b = connect("127.0.0.1", "3306", "root", "root", "");
        String resultDB = createDB(targetDB);
        disconnect();
    }

    /**
     * 将主题数据库的相关信息写入数据库
     */
    public void targetConfig(Map<String, String> config) throws Exception {
        limites = 0;
        rowTotal = 0;
        rowProcess = 0;
        pointCount = 0;
        requstClass requstclass = new requstClass();
        System.out.println(config.get("subjectSchame"));
        System.out.println(config);
        requstclass.setDataTargetDBname(config.get("subjectSchame"));
        requstclass.setDataTargetTableName(config.get("subjectTable"));
        requstclass.setDataTargetCreateUser(config.get("creator"));
        requstclass.setDataTargetDescription(config.get("subjectDescription"));
        requstclass.setDataTargetLimites(config.get("limites"));
        String s = config.get("subjectUUID");
        dbName = config.get("subjectSchame");
        tableName = config.get("subjectTable");
        Date date = new Date();
        Timestamp timeStamp = new Timestamp(System.currentTimeMillis());
        //String s = UUID.randomUUID().toString();//用来生成数据库的主键id非常不错..
        requstclass.setTaskId(s);
        uuid = s;
        Double process = 0.0;
        String b = connect("127.0.0.1", "3306", "root", "root", "Dataset");
        String sql = "INSERT INTO datatargetconf(taskId,dataTargetDBname,dataTargetDescription,dataTargetCreateUser,dataTargetTableName,dataTargetLimites,timeCreated,datatargetProcess) VALUES (";
        PreparedStatement ps = null;
        ps = conn.prepareStatement("INSERT INTO datatargetconf(taskId,dataTargetDBname,dataTargetDescription,dataTargetCreateUser,dataTargetTableName,dataTargetLimites,timeCreated,datatargetProcess) VALUES (?,?,?,?,?,?,?,?)");
        sql = sql + requstclass.getTaskId() + "," + requstclass.getDataTargetDBname() + "," + requstclass.getDataTargetDescription() + "," + requstclass.getDataTargetCreateUser() + "," + requstclass.getDataTargetTableName() + "," + requstclass.getDataTargetLimites() + "," + date + "," + process + ");";
        ps.setString(1, requstclass.getTaskId());
        ps.setString(2, requstclass.getDataTargetDBname());
        ps.setString(3, requstclass.getDataTargetDescription());
        ps.setString(4, requstclass.getDataTargetCreateUser());
        ps.setString(5, requstclass.getDataTargetTableName());
        ps.setString(6, requstclass.getDataTargetLimites());
        ps.setTimestamp(7, timeStamp);
        ps.setDouble(8, process);
        System.out.println(sql);
        ps.executeUpdate();
        ps.closeOnCompletion();
        disconnect();
    }
}//end main
